package Product;
use lib "/data/tools/repository/perl-5.8.8/lib/site_perl/5.8.8/";
use strict;

#use DBI;
use Spreadsheet::ParseExcel;

#use Run;



sub new
{
my $self = {};
return (bless $self);
}

sub excel
{
my ($self,$file,$book)= @_;
my $oExcel = new Spreadsheet::ParseExcel;
die "You must provide a filename to $0 to be parsed as an Excel file" unless @_;
my $oBook = $oExcel->Parse($file);
$self->{$book}{'oBook'} = $oBook;
return ($self);
}


sub getsheet
{
my ($self,$book) = @_;
my $oBook = $self->{$book}{'oBook'};
for(my $iSheet=0; $iSheet < $oBook->{SheetCount} ; $iSheet++)
        {
        my $oWkC;
        my $oWkS = $oBook->{Worksheet}[$iSheet];
        if ($oWkS->{Name} eq "Products Tree")
                {
                 $self->{$book}{'SHEET'} = $iSheet;
		}
	last;
	}
return ($self);
}


sub gethead
{
my ($self,$book,@field) = @_;
my $oBook = $self->{$book}{'oBook'};
	my $oWkC;
	my $oWkS = $oBook->{Worksheet}[$self->{$book}{'SHEET'}];
		 for(my $iR = $oWkS->{MinRow} ;defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ;$iR++)
 			{
				for(my $iC = $oWkS->{MinCol} ; defined $oWkS->{MaxCol} && $iC <= $oWkS->{MaxCol} ;  $iC++)
					{
					$oWkC = $oWkS->{Cells}[$iR][$iC];
						my $i =0;
						if ($oWkC)
						{
						my $value = $oWkC->Value;
						if (($value =~ /\n/) or ($value =~ /\s/))
							{
							$value =~ s/\n//g;
							$value =~ s/\s//g;
							}
						foreach my $field (@field)
						{	
							if ($value =~ /$field/i)
							{
							$self->{$field}{'IC'} = $iC;
							$self->{$book}{'IR'} = $iR;
							splice (@field,$i,1);
							}
						$i++;
						}
						}
						if ((! @field) and (defined $self->{$book}{'IR'}))
						{
						return ();
						}
						
					}
			 }
		
	
}


sub getpd
{
	my ($self,$CommercialName,$book,@field) = @_;
	my $oBook =$self->{$book}{'oBook'};
	unless (defined $self->{$book}{'SHEET'})
		{
		$self->getsheet($book);
		}
		$self->gethead($book,@field);

	my $oWkS = $oBook->{Worksheet}[$self->{$book}{'SHEET'}];


	if ($book eq "PRODUCT")
		{
		my $commname_elle=$CommercialName;
		my $row = $self->{$book}{'IR'};
		$row = $row+1;
		while (my $oWkC = $oWkS->{Cells}[$row][$self->{'CommercialName'}{'IC'}]) 
			{
			my $value =$oWkC->Value;
			my $value_elle=$value;
			if ($value)
			{
			if (($CommercialName =~ /ELLE N/i) and ($value =~ /ELLE N/i))
				{
				$commname_elle=~ s/\D//g;
				$value_elle=~ s/\D//g;
				if ($commname_elle eq $value_elle)
				{
				$value=$CommercialName;
				}
				}
	
			if (($value =~ /$CommercialName/i) or ($commname_elle eq $value_elle))
				{
				my $row_a = $row;
				while (! $oWkS->{Cells}[$row_a][$self->{'FamillyName'}{'IC'}]->Value)
					{
					$row_a--; 
					}
				$self->{$CommercialName}{'CommercialName'}=$value;
			        $self->{$CommercialName}{'FamillyName'}=$oWkS->{Cells}[$row_a][$self->{'FamillyName'}{'IC'}]->Value;
				$row_a = $row;
				while (! $oWkS->{Cells}[$row_a][$self->{'FriendlyName2'}{'IC'}]->Value)
				        {
				        $row_a--;
				        }
				$self->{$CommercialName}{'FriendlyName2'}=$oWkS->{Cells}[$row_a][$self->{'FriendlyName2'}{'IC'}]->Value;
				last; 
				}
			}
			$row++;
			}
			if (! defined $self->{$CommercialName}{'CommercialName'})
			{
			$self->{'PRODUCT'}{'ERROR'}=1;
			}
		}

	return ($self);
}




1;
